/* @(#)GSLoginAction.java    
*
* Project: BUS
*
* Modify Information:
* =============================================================================
*   Author       Date       Description
*   ------------ ---------- ---------------------------------------------------
*   Simonyi Peng 2006-2-7    建立
*   Jenny   Li   2006-2-16  添加方法queryUncommitOrderAndDetail，queryOrder，updateOrderStatus，queryOrderByOpt
* 	simonyi peng 2006-2-16  添加方法selectOrderByCode
*   Road Zheng   2006-2-16  添加方法getOrderByTempID
*   owen  xia    2006-3-18  添加方法queryBusCancelOrder
*   Simonyi Peng 2006-3-21  添加方法insert,selectOrderByCode
*   Jenny   Li   2006-4-1   修改方法modifyCancelBusOrder，添加参数
* Copyright Notice:
* =============================================================================
*    Copyright (c) 2001-2006 Beijing HiTRUST Technology Co., Ltd. 1808 Room, 
*        Science & Technology Building, No. 9 South Street, Zhong Guan Cun, 
*        Haidian District, Beijing ,100081, China All rights reserved.
*
*    This software is the confidential and proprietary information of
*        Beijing HiTRUST Technology Co., Ltd. ("Confidential Information").
*        You shall not disclose such Confidential Information and shall use it
*        only in accordance with the terms of the license agreement you entered
*        into with HiTRUST.
*
* Warning:
* =============================================================================
* 
*/
package com.hitrust.gs.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import system.LoginContext;

import com.hitrust.gs.bean.GSManufactureBean;
import com.hitrust.gs.bean.GSOperatorBean;
import com.hitrust.gs.bean.GSOrderAnaDetailAndCategoryBean;
import com.hitrust.gs.bean.GSOrderBean;
import com.hitrust.gs.bean.GSOrderDetailBean;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.service.GSBaseManager;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.util.lang.IDGenerator;
import com.hitrust.gs.vo.GSBusOrderCancelOfWorkingVO;
import com.hitrust.gs.vo.GSBusOrderCancelVO;
import com.hitrust.gs.vo.GSBusOrderRefundVO;
import com.hitrust.gs.vo.GSOrderManufactureVO;
import com.hitrust.gs.vo.GSOrderVO;

/**
 * @author simonyi
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class GSBusOrderDAO extends GSBaseDAO {
       
   
    /**
     * 根据订单编号查询订单基本信息
     * @param aOrderTempID 订单编号
     * @return GSOrderBean 订单基本信息
     * @throws GSException
     * @author Road Zheng
     */
    public GSOrderBean getOrderByTempID(String aOrderTempID) throws GSException {
    	GSOrderBean tResult = null;
        Connection tConn = this.getConnetion();
        ArrayList tArrayList = null;
        try{
            StringBuffer tSql = new StringBuffer("select * "); 
            tSql.append("from GS_ORDER ");
            tSql.append("where GS_ORDER_TEMPID = ?" );
            GSOrderBean tGSOrderBean = new GSOrderBean();
            Object[] tObj = {aOrderTempID};
            tArrayList = this.executeQuery(tConn,tGSOrderBean,tSql.toString(),tObj);
        }catch(Exception sqle){
            throw new GSException (GSException.E20043,GSException.E20043_MSG);
        }finally{
            close(tConn);
        }
        if(tArrayList != null && tArrayList.size() > 0) {
        	tResult = (GSOrderBean) tArrayList.get(0);
        }
        return tResult;
    }
   
    
    /**
     * 修改订单状态为取消
     * @param aOrderId      订单编号
     * @param aStatus       订单状态
     * @param aConn         DB连接
     * @return
     * @throws GSException
     */
    public boolean updateOrderStatusToCancel(String aOrderId,String aOperatorLevel,Connection aConn) throws GSException{
        String tSql = "";
        if(aOperatorLevel.equals("11")) {
        	tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_ORDER_CREATE_DATE=? where GS_ORDER_TEMPID=?";
        } else if(aOperatorLevel.equals("12")) {
        	tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_FIRSTAUDIT_DATE=? where GS_ORDER_TEMPID=?";
        } else if(aOperatorLevel.equals("13")) {
        	tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_REVIEW_DATE=? where GS_ORDER_TEMPID=?";
        }
        String tCurDT = DateUtil.getCurrentTime("DT","AD");
        tCurDT = tCurDT.substring(0,4)+"-" + tCurDT.substring(4,6) + "-" + tCurDT.substring(6,8) + " " + tCurDT.substring(8,10)
                    + ":" + tCurDT.substring(10,12) + ":" + tCurDT.substring(12,14);
         Object tObj[] = {"21",tCurDT,aOrderId};
        boolean tFlag = false;
        try{
            tFlag = this.executeUpdate(aConn,tSql,tObj);
            
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19994,GSException.E19994_MSG);
        }
        return tFlag;
    }
    
    /**
     * 修改订单状态到原先状态
     * @param aOrderId      订单编号
     * @param aStatus       订单状态
     * @param aConn         DB连接
     * @return
     * @throws GSException
     */
    public boolean updateOrderStatusToBefore(String aOrderId,String aOperatorLevel,String aStatus,Connection aConn) throws GSException{
        String tSql = "";
        if(aOperatorLevel.equals("12")) {
        	tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_FIRSTAUDIT_DATE=? where GS_ORDER_TEMPID=?";
        } else if(aOperatorLevel.equals("13")) {
        	tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_REVIEW_DATE=? where GS_ORDER_TEMPID=?";
        }
        String tCurDT = DateUtil.getCurrentTime("DT","AD");
        tCurDT = tCurDT.substring(0,4)+"-" + tCurDT.substring(4,6) + "-" + tCurDT.substring(6,8) + " " + tCurDT.substring(8,10)
                    + ":" + tCurDT.substring(10,12) + ":" + tCurDT.substring(12,14);
         Object tObj[] = {aStatus,tCurDT,aOrderId};
        boolean tFlag = false;
        try{
            tFlag = this.executeUpdate(aConn,tSql,tObj);
            
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19994,GSException.E19994_MSG);
        }
        return tFlag;
    }
    
    public boolean checkOrderDetailStatus(String aOrderTempID) 
    	throws GSException{
    	boolean tResult = false;
    	String tSql = "select count(*) from GS_ORDER_DETAIL where gs_order_tempid = ?";

		int i = 0;
		int k = 0;

		Object[] tObj = {aOrderTempID};
		try{
			i = this.queryRowNum(tSql,tObj);
			tSql = "select count(*) from GS_ORDER_DETAIL where gs_order_tempid = ? and GS_ORDER_DETAIL_STATUS = '1'";
			k= this.queryRowNum(tSql,tObj);
			if(i==k) tResult = true;
			else {
				tSql = "select count(*) from GS_ORDER_DETAIL where gs_order_tempid = ? and GS_ORDER_DETAIL_STATUS = '2'";
				k= this.queryRowNum(tSql,tObj);
				if(i==k) tResult = true;
				else {
					tSql = "select count(*) from GS_ORDER_DETAIL where gs_order_tempid = ? and GS_ORDER_DETAIL_STATUS = '3'";
					k= this.queryRowNum(tSql,tObj);
					if(i==k) tResult = true;
					else {
						tSql = "select count(*) from GS_ORDER_DETAIL where gs_order_tempid = ? and GS_ORDER_DETAIL_STATUS = '4'";
						k= this.queryRowNum(tSql,tObj);
						if(i==k) tResult = true;
						else {
							tSql = "select count(*) from GS_ORDER_DETAIL where gs_order_tempid = ? and GS_ORDER_DETAIL_STATUS = '5'";
							k= this.queryRowNum(tSql,tObj);
							if(i==k) tResult = true;
							else {
								tSql = "select count(*) from GS_ORDER_DETAIL where gs_order_tempid = ? and GS_ORDER_DETAIL_STATUS = '6'";
								k= this.queryRowNum(tSql,tObj);
								if(i==k) tResult = true;								
							}
						}
					}
				}
			}
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19999,GSException.E19999_MSG);
		}
    	
    	return tResult;
    }
    
    /**
     * 查询取消订单详细列表
     */
    public ArrayList queryBusCancelOrder(Connection aConn, GSBusOrderCancelVO aGSBusOrderCancelVO) throws GSException {

        StringBuffer tSql = new StringBuffer(GSOrderDetailDAO.getOrderAndDetailAndCategorySql());
        ArrayList tList = null;

        if (aGSBusOrderCancelVO.orderid != null) {
            tSql.append(" and det.GS_ORDER_DETAIL_ID like '%" + aGSBusOrderCancelVO.orderid + "%'");
        }
        if (!aGSBusOrderCancelVO.orderDetailStatus.equals("00")) {
            tSql.append(" and det.GS_ORDER_DETAIL_STATUS = '" + aGSBusOrderCancelVO.orderDetailStatus + "'");
        } else {
            tSql.append(" and det.GS_ORDER_DETAIL_STATUS in ('7','9','11')");
        }
        if (aGSBusOrderCancelVO.orderDeptId != null) {
                tSql.append(" and ord.GS_WORKCORP_ID  like '" + aGSBusOrderCancelVO.orderDeptId + "%'");
        }
        if (aGSBusOrderCancelVO.concentricityId != null) {
            tSql.append(" and det.GS_CONCENTRICITY_ID like '" + aGSBusOrderCancelVO.concentricityId + "%'");
        }
        if (aGSBusOrderCancelVO.manufInfo != null) {
            tSql.append(" and (pro.NAME like '%" + aGSBusOrderCancelVO.manufInfo + "%'");
            tSql.append(" or pro.MODEL like '%" + aGSBusOrderCancelVO.manufInfo + "%'");
            tSql.append(" or pro.SPEC like '%" + aGSBusOrderCancelVO.manufInfo + "%'");
            tSql.append(" or pro.PRODUCT_BRAND like '%" + aGSBusOrderCancelVO.manufInfo + "%'");
            tSql.append(" or pro.MANUFATURER like '%" + aGSBusOrderCancelVO.manufInfo + "%')");
        }
        if (aGSBusOrderCancelVO.providerId != null) {
            tSql.append(" and prv.NAME like '%" + aGSBusOrderCancelVO.providerId + "%'");
        }
        if (aGSBusOrderCancelVO.manufType1!=null) {
            if(aGSBusOrderCancelVO.manufType1.equals("0")){
                tSql.append(" and cat.code like '%'");
            }else if(aGSBusOrderCancelVO.manufType2.equals("0")){
                tSql.append(" and (cat.CATEGORY_ID1 ='" + aGSBusOrderCancelVO.manufType1 + "'");
                tSql.append(" or cat.code ='" + aGSBusOrderCancelVO.manufType1 + "')");
            }else if(aGSBusOrderCancelVO.manufType3.equals("0")){
                tSql.append(" and (cat.CATEGORY_ID2 ='" + aGSBusOrderCancelVO.manufType2 + "'");
                tSql.append(" or cat.code ='" + aGSBusOrderCancelVO.manufType2 + "')");
            }else{
                tSql.append(" and cat.code ='" + aGSBusOrderCancelVO.manufType3 + "'");
            }
        }
        //图件号

        if(aGSBusOrderCancelVO.drawingpiececode != null && !aGSBusOrderCancelVO.drawingpiececode.equals("")){
                    tSql.append(" and pro.DRAWING_PIECE_CODE like '%" + aGSBusOrderCancelVO.drawingpiececode.trim() + "%'");
                }
        tSql.append(" and to_char(to_date(ord.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYYMMDD') >='"
                + aGSBusOrderCancelVO.orderCreateDateStr + "'");
        tSql.append(" and to_char(to_date(ord.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYYMMDD') <='"
                + aGSBusOrderCancelVO.orderCreateDateEnd + "'");

        tSql.append(" order by det.GS_LASTMODIFY_DATE desc");
        GSLogWriter.debug(this, tSql.toString());
        Object[] tObj = {};
        try {
            tList = this.executeQuery(aConn, new GSOrderAnaDetailAndCategoryBean(), tSql.toString(), tObj);
        } catch (Exception e) {
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return tList;
    }
    
    /**
     * 执行巴士物资订单明细的取消状态
     * @return  boolean 
     * @throws  GSException
     */
    public boolean modifyCancelBusOrder(Connection aConn,String aOrderDetailStatus,String aOrderDetailId,
            LoginContext context,String aOperDate,String aContents)throws GSException{
        
        String tSql= "update GS_ORDER_DETAIL set GS_ORDER_DETAIL_STATUS = ?,GS_CANCEL_PERSON=?," +
                "GS_CANCEL_DATE=?,GS_CANCEL_CAUSE=?,GS_LASTMODIFER_ID=?,GS_LASTMODIFY_CONTENTS=?," +
                "GS_LASTMODIFY_DATE=? where GS_ORDER_DETAIL_ID = ? ";   
        Object tObj[] = new Object[8];
        tObj[0] = aOrderDetailStatus;
        tObj[1] = context.getLoginUserId();
        tObj[2] = aOperDate;
        tObj[3] = aContents;
        tObj[4] = context.getLoginUserId();
        tObj[5] = GSBaseManager.getOrderDetailStatusName(aOrderDetailStatus);
        tObj[6] = aOperDate;   
        tObj[7] = aOrderDetailId;
       
        boolean tResult = false;
        try{
            tResult = executeUpdate(aConn,tSql,tObj);
        }catch(Exception ex){
            GSLogWriter.error(this,ex.getMessage());
            throw new GSException(ex.getMessage());
        }
        return tResult;
    }
    
    /**
     * 建立一个新的订单
     * @param vo 订单数据值对象
     * @param ary 订单详细数据对象矢量
     * @throws GSException
     */
    public String insert(GSOperatorBean operBean,GSOrderVO vo,ArrayList ary) throws GSException {
        
        Connection conn = this.getConnetion();
        
        IDGenerator idg = new IDGenerator();
        
        String returnValue = "";
        try{
            conn.setAutoCommit(false);
            
            //构造订单号
            long id = idg.generatorID(vo.orderDeptId,"01");
            String orderId = vo.orderDeptId + idg.formatID(id,5);
            returnValue = orderId;
            
            //0 如果选择了直接审批通过，将订单(明细)状态修正为'7'已核准，否则'6'已提交
            String isReviewNeeded = "6";//"on".equals(vo.isReviewNeeded)?"7":"6"; 
            
            //1 构造生成时间
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String createDate = sdf.format(new Date(System.currentTimeMillis()));
            
            //2 构造订单明细sql语句
            PreparedStatement pstmt = null;
            String sqlm = "insert into GS_ORDER_DETAIL (GS_ORDER_TEMPID,GS_ORDER_DETAIL_ID," + 
        	"GS_MANUFACTURE_ID,GS_CONCENTRICITY_ID,GS_ORDER_COUNT," + 
        	"GS_DELIVER_GOODS_DATE,GS_DELIVER_GOODS_TIME,GS_ORDER_DETAIL_STATUS," + 
        	"GS_PROVIDER_ID,GS_LASTMODIFER_ID,GS_LASTMODIFY_CONTENTS,GS_LASTMODIFY_DATE," +
        	"GS_GOODS_UNITPRICE,GS_IS_BALANCEED,GS_IS_PROV_BALANCEED,GS_BALANCE_CATEGORY_NO,gs_balance_mode) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sqlm);

            //3 计算订单总价
            double totalPrice = 0;
            BigDecimal buf = new BigDecimal("0");
            
            //4 对每一个产品
            for(int i=0;i<ary.size();i++){
                GSOrderManufactureVO dvo = (GSOrderManufactureVO)ary.get(i);
                GSManufactureDAO mdao = new GSManufactureDAO();
                GSManufactureBean mbean = mdao.getManufacture(dvo.manufactureid);
                
                //计算产品的总价
                BigDecimal bd = new BigDecimal(new Double(mbean.getGs_manufacture_unitprice()).toString());
                //订单总价=产品单价*产品数量+初始订单总价
                buf = bd.multiply(new BigDecimal(""+dvo.ordercount)).add(buf);
                
                //构造订单明细信息号
                String orderDetailId = orderId + idg.formatID(i,2);

                pstmt.setString(1,orderId);
                pstmt.setString(2,orderDetailId);
                pstmt.setString(3,dvo.manufactureid);
                pstmt.setString(4,dvo.concentricityId);
                pstmt.setDouble(5,Double.parseDouble(dvo.ordercount));
                pstmt.setString(6,dvo.deliverdate.replace('/','-'));
                pstmt.setString(7,dvo.delivertime);
                pstmt.setString(8,isReviewNeeded);
                pstmt.setString(9,mbean.getGs_provider_id());
                pstmt.setString(10,operBean.getGs_oper_id());
                pstmt.setString(11,"申购单录入");
                pstmt.setString(12,createDate);
                pstmt.setDouble(13,mbean.getGs_manufacture_unitprice());
                pstmt.setString(14,"0");//表示尚未纳入结算数据
                pstmt.setString(15,"0");//表示尚未纳入供应商结算数据
                pstmt.setString(16,dvo.balanceCategory);
                pstmt.setString(17, dvo.mode);//压库、买断
                //存入数据库
                pstmt.addBatch();
            }


            //5 构造订单sql语句
            String sqlo = "insert into GS_ORDER (GS_ORDER_TEMPID,GS_WORKCORP_ID,"+
        	"GS_ORDER_CATEGORY,GS_ORDER_STATUS,GS_NOTES,GS_RECORDER_ID,"+
        	"GS_ORDER_CREATE_DATE,GS_ORDER_SUM,GS_DELIVER_GOODS_ADDR," +
        	"GS_ORDER_CONTACT_NAME,GS_ORDER_CONTACT_PHONE," +
        	"GS_DELIVER_MODE,GS_BALANCE_MODE) values ('" +
        	orderId + "','" + vo.orderDeptId + "',0," + isReviewNeeded + ",'" + vo.notes + "','" +
        	vo.operid + "','" + createDate + "'," + buf.doubleValue() + ",'" +
        	vo.deliverAddr + "','" + vo.linkMan + "','" + vo.linkTel + "','" + 
        	vo.deliverMode + "','" + vo.balanceMode + "')"; 
 
            PreparedStatement pstmto = conn.prepareStatement(sqlo);
            pstmto.executeUpdate();

            //6 提交订单明细
            pstmt.executeBatch();
            conn.commit();
            if(pstmt!=null) pstmt.close();
            if(pstmto!=null) pstmto.close();
            
        }catch(Exception e){
            try{
            conn.rollback();
            }catch(SQLException sqle){sqle.printStackTrace();}
            throw new GSException (GSException.E20057,GSException.E20057_MSG);
        }finally{
            try{
            conn.setAutoCommit(true);
            close(conn);
            }catch(SQLException sqle){}
        }
        
        return returnValue;

    }

	public GSOrderBean selectOrderByCode(String orderCode) throws GSException {
		GSOrderBean tGSOrderBean = new GSOrderBean();
		String sql = "select * from gs_order where gs_order_tempid = ?";

		ArrayList tList = null;

		Object[] tObj = { orderCode };
		try {
			tList = this.executeQuery(new GSOrderBean(), sql, tObj);
			if (tList.size() == 1) {
				tGSOrderBean = (GSOrderBean) tList.get(0);
			} else {
				// throw new
				// GSException(GSException.BME20041,GSException.BME20041_MSG);
			}
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E20071, GSException.E20071_MSG);
		}

		return tGSOrderBean;

	}
	
	// add by Jackey start

	/**
	 * 查询退货订单详细列表
	 */
	public ArrayList queryBusRefundOrder(Connection aConn, GSBusOrderRefundVO aGSBusOrderRefundVO,ArrayList aOrg) throws GSException {

		GSBusOrderCancelOfWorkingVO tOrderVO = new GSBusOrderCancelOfWorkingVO();

		StringBuffer tSql = new StringBuffer("select A.GS_ORDER_TEMPID as P1,A.GS_ORDER_CREATE_DATE as P2,");
		tSql.append("A.GS_ORDER_STATUS as P3,C.GS_MANUFACTURE_NAME as P4,B.GS_ORDER_COUNT as P5,");
		tSql.append("A.GS_WORKCORP_ID as P6 ,E.GS_ORG_NAME as P7,A.GS_RECORDER_ID as P8,");
		tSql.append("B.GS_CONCENTRICITY_ID as P9,A.GS_DELIVER_MODE as P10,A.GS_BALANCE_MODE as P11,C.GS_MANUFACTURE_SPEC as P12,");
		tSql.append("C.GS_MANUFACTURE_TYPE as P13,C.GS_MANUFACTURE_BRAND as P14,C.GS_QUANT_UNIT as P15,");
		tSql.append("A.GS_DELIVER_GOODS_ADDR as P16,A.GS_ORDER_CONTACT_NAME as P17,A.GS_ORDER_CONTACT_PHONE as P18,");
		tSql.append("B.GS_ORDER_DETAIL_ID as P19,B.GS_ORDER_DETAIL_STATUS as P20,C.GS_IS_TRADEFLAT_MANUFACTURE as P21,");
		tSql.append("F.GS_PROVIDER_SHORTNAME as P22,B.GS_DELIVER_GOODS_DATE as P23,B.GS_DELIVER_GOODS_TIME as P24,");
		tSql.append("B.GS_GOODS_UNITPRICE as P25,F.GS_PROVIDER_ID as P26,F.GS_IS_TRADEFLAT_PROVIDER as P27 from GS_ORDER A,GS_ORDER_DETAIL B,GS_MANUFACTURE C,GS_ORDER_DEPT_INFO D,GS_ORGANIZATION E,GS_PROVIDER F,");
		tSql.append("GS_MANUFACTURE_CATEGORY G");
		tSql.append(" where A.GS_ORDER_TEMPID=B.GS_ORDER_TEMPID and B.GS_CONCENTRICITY_ID=C.GS_CONCENTRICITY_ID");
		tSql.append(" and (b.gs_manufacture_id is null or (b.gs_manufacture_id is not null and b.gs_manufacture_id = c.gs_manufacture_id))");
		// tSql.append(" and (A.GS_ORDER_STATUS ='2' or
		// A.GS_ORDER_STATUS='3')");
		tSql.append(" and A.GS_WORKCORP_ID=D.GS_ORG_ID and A.GS_WORKCORP_ID=E.GS_ORG_ID");
		tSql.append(" and A.GS_DELIVER_GOODS_ADDR = D.GS_DELIVER_GOODS_ADDR");
		tSql.append(" and B.GS_PROVIDER_ID = F.GS_PROVIDER_ID");
		tSql.append(" and C.GS_MANUFACTURE_CATEGORY_ID = G.GS_MANUFACTURE_CATEGORY_ID");
		ArrayList tList = null;

		if (aGSBusOrderRefundVO.orderid != null) {
			tSql.append(" and B.GS_ORDER_DETAIL_ID like '%" + aGSBusOrderRefundVO.orderid + "%'");
		}
		if (!aGSBusOrderRefundVO.orderDetailStatus.equals("00")) {
			tSql.append(" and B.GS_ORDER_DETAIL_STATUS = '" + aGSBusOrderRefundVO.orderDetailStatus + "'");
		} else {
			tSql.append(" and (B.GS_ORDER_DETAIL_STATUS ='7' or B.GS_ORDER_DETAIL_STATUS ='9' or B.GS_ORDER_DETAIL_STATUS ='10' or B.GS_ORDER_DETAIL_STATUS ='11')");
			   }
		if (aGSBusOrderRefundVO.orderDeptId != null) {
			if (aGSBusOrderRefundVO.orderDeptId.equals("0000")) {
				 if(aOrg != null && aOrg.size() > 0) {
					tSql.append(" and A.GS_WORKCORP_ID in(");
					for (int i = 0; i < aOrg.size(); i++) {
						String tStr[] = (String[]) aOrg.get(i);
						if (i == aOrg.size() - 1) {
							tSql.append("'" + tStr[0] + "')");
						} else {
							tSql.append("'" + tStr[0] + "',");
						}
					}
				}
			} else {
				tSql.append(" and A.GS_WORKCORP_ID ='" + aGSBusOrderRefundVO.orderDeptId + "'");
			}
		}
		if (aGSBusOrderRefundVO.deliverGoodsAddrId.equals("0000")) {
			tSql.append(" and D.GS_DELIVER_GOODS_ADDR_ID like '%'");
		} else {
			tSql.append(" and D.GS_DELIVER_GOODS_ADDR_ID ='" + aGSBusOrderRefundVO.deliverGoodsAddrId + "'");
		}
		if (aGSBusOrderRefundVO.concentricityId != null) {
			tSql.append(" and B.GS_CONCENTRICITY_ID like '" + aGSBusOrderRefundVO.concentricityId + "%'");
		}
		if (aGSBusOrderRefundVO.manufInfo != null) {
			tSql.append(" and (C.GS_MANUFACTURE_NAME like '%" + aGSBusOrderRefundVO.manufInfo + "%'");
			tSql.append(" or C.GS_MANUFACTURE_TYPE like '%" + aGSBusOrderRefundVO.manufInfo + "%'");
			tSql.append(" or C.GS_MANUFACTURE_SPEC like '%" + aGSBusOrderRefundVO.manufInfo + "%'");
			tSql.append(" or C.GS_MANUFACTURE_BRAND like '%" + aGSBusOrderRefundVO.manufInfo + "%'");
			tSql.append(" or C.GS_MANUFACTURER_NAME like '%" + aGSBusOrderRefundVO.manufInfo + "%')");
		}
		if (aGSBusOrderRefundVO.providerId != null) {
			tSql.append(" and F.GS_PROVIDER_NAME like '%" + aGSBusOrderRefundVO.providerId + "%'");
		}
		if (aGSBusOrderRefundVO.manufType1!=null) {
//			  tSql.append(" and C.GS_MANUFACTURE_CATEGORY_ID like '" + aGSBusOrderCancelVO.category + "%'");
			if(aGSBusOrderRefundVO.manufType1.equals("0")){
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID like '%'");
			}else if(aGSBusOrderRefundVO.manufType2.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID1 ='" + aGSBusOrderRefundVO.manufType1 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID ='" + aGSBusOrderRefundVO.manufType1 + "')");
			}else if(aGSBusOrderRefundVO.manufType3.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID2 ='" + aGSBusOrderRefundVO.manufType2 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID ='" + aGSBusOrderRefundVO.manufType2 + "')");
			}else{
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID ='" + aGSBusOrderRefundVO.manufType3 + "'");
			}

            
		}
		tSql.append(" and to_char(to_date(A.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYYMMDD') >='"
				+ aGSBusOrderRefundVO.orderCreateDateStr + "'");
		tSql.append(" and to_char(to_date(A.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYYMMDD') <='"
				+ aGSBusOrderRefundVO.orderCreateDateEnd + "'");

		tSql.append(" order by B.GS_LASTMODIFY_DATE desc");
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, tOrderVO, tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}
		return tList;
	}
	
	/**
	 * 执行巴士物资订单退货
	 * @return  boolean 
	 * @throws  GSException
	 */
	public String doRefundBusOrder(Connection aConn,String aOrderDetailStatus,String aOrderDetailId,
			GSOperatorBean aOperBean,String aOperDate,String aRefundcount,String aContents)throws GSException{
        
		String returnValue = "";
        
		try{
	        //得到退货订单子表信息
			GSOrderDetailDAO tGSOrderDetailDAO = new GSOrderDetailDAO();
			GSOrderDetailBean tGSOrderDetailBean = tGSOrderDetailDAO.queryOrderDetailByDetailId(aConn,aOrderDetailId);
	        if (tGSOrderDetailBean == null)
	        {
				throw new GSException("要退货单证查询失败！");
	        }
	               
	        //得到退货订单主表信息
	        GSBusOrderDAO tGSBusOrderDAO = new GSBusOrderDAO();
			GSOrderBean tGSOrderBean = tGSBusOrderDAO.getOrderByTempID(tGSOrderDetailBean.getGs_order_temp_id());        
	        
	        String orderDeptId = tGSOrderBean.getGs_workcorp_id();
	        
			//构造订单号
			IDGenerator idg = new IDGenerator();
			long id = idg.generatorID(orderDeptId,"03");
			String orderId = orderDeptId + idg.formatID(id,4) + "T";
			returnValue = orderId;
			
			//1 构造生成时间
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String createDate = sdf.format(new Date(System.currentTimeMillis()));
            
			//2 构造订单明细sql语句
			PreparedStatement pstmt = null;
			String sqlm = "";
            
			sqlm = "insert into GS_ORDER_DETAIL (GS_ORDER_TEMPID,GS_ORDER_DETAIL_ID," + 
					"GS_MANUFACTURE_ID,GS_CONCENTRICITY_ID,GS_ORDER_COUNT," + 
					"GS_DELIVER_GOODS_DATE,GS_DELIVER_GOODS_TIME,GS_ORDER_DETAIL_STATUS," + 
					"GS_PROVIDER_ID,GS_LASTMODIFER_ID,GS_LASTMODIFY_CONTENTS,GS_LASTMODIFY_DATE," +
					"GS_GOODS_UNITPRICE,GS_IS_BALANCEED,GS_IS_PROV_BALANCEED,GS_BALANCE_CATEGORY_NO," +
					"LINK_ORDER_DETAIL_ID,REQUEST_BACK_REASON,REQUEST_BACK_TIME,REQUEST_BACK_PERSON," +
					"REQUEST_BACK_QTY) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmt = aConn.prepareStatement(sqlm);
			
			//构造订单明细信息号
			String orderDetailId = orderId + idg.formatID(0,2);
			
			pstmt.setString(1,orderId);
			pstmt.setString(2,orderDetailId);
			pstmt.setString(3,tGSOrderDetailBean.getGs_manufacture_id());
			pstmt.setString(4,tGSOrderDetailBean.getGs_concentricity_id());
			pstmt.setDouble(5,Double.parseDouble(aRefundcount));
			pstmt.setString(6,tGSOrderDetailBean.getGs_deliver_goods_confirm_date().replace('/','-'));
			pstmt.setString(7,tGSOrderDetailBean.getGs_deliver_goods_time());
			//退货明细状态设置为：退货申请
			pstmt.setString(8,GSBaseConstants.ORDER_REFUND_APPLITION);
			pstmt.setString(9,tGSOrderDetailBean.getGs_provider_id());
			pstmt.setString(10,aOperBean.getGs_oper_id());
			pstmt.setString(11,"退货申请录入");
			pstmt.setString(12,createDate);
			pstmt.setDouble(13,tGSOrderDetailBean.getGs_goods_unitprice());
			pstmt.setString(14,tGSOrderDetailBean.getGs_is_balanced());
			pstmt.setString(15,tGSOrderDetailBean.getGs_is_prov_balanced());
			pstmt.setString(16,tGSOrderDetailBean.getGs_balance_categor_no());
			
			//退货信息设置
			pstmt.setString(17,aOrderDetailId);
			pstmt.setString(18,aContents);
			pstmt.setString(19,createDate);
			pstmt.setString(20,aOperBean.getGs_oper_id());
			pstmt.setDouble(21,Double.parseDouble(aRefundcount)); 
                
			//存入数据库
			pstmt.addBatch();
			
			//3 构造订单sql语句
			String sqlo = "insert into GS_ORDER (GS_ORDER_TEMPID,GS_WORKCORP_ID,"+
					"GS_ORDER_CATEGORY,GS_ORDER_STATUS,GS_NOTES,GS_RECORDER_ID,"+
					"GS_ORDER_CREATE_DATE,GS_ORDER_SUM,GS_DELIVER_GOODS_ADDR," +
					"GS_ORDER_CONTACT_NAME,GS_ORDER_CONTACT_PHONE," +
					"GS_DELIVER_MODE,GS_BALANCE_MODE,"+
					"GS_FIRSTAUDITOR_ID,GS_FIRSTAUDIT_DATE,GS_FIRSTAUDIT_ADVICES,"+
					"GS_REVIEWER_ID,GS_REVIEW_DATE,GS_REVIEW_ADVICES) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";		
					
			PreparedStatement pstmto = aConn.prepareStatement(sqlo);
			pstmto.setString(1,orderId);
			pstmto.setString(2,orderDeptId);
			//退货为：2
			pstmto.setString(3,"2");
			pstmto.setString(4,GSBaseConstants.ORDER_REFUND_APPLITION);
			pstmto.setString(5,tGSOrderBean.getGs_notes());
			pstmto.setString(6,tGSOrderBean.getGs_recorder_id());
			pstmto.setString(7,createDate);
			pstmto.setDouble(8,tGSOrderBean.getGs_order_sum());
			pstmto.setString(9,tGSOrderBean.getGs_deliver_goods_addr());
			pstmto.setString(10,tGSOrderBean.getGs_order_contact_name());
			pstmto.setString(11,tGSOrderBean.getGs_order_contact_phone());
			pstmto.setString(12,tGSOrderBean.getGs_deliver_mode());
			pstmto.setString(13,tGSOrderBean.getGs_balance_mode());
			pstmto.setString(14,tGSOrderBean.getGs_firstauditor_id());
			pstmto.setString(15,tGSOrderBean.getGs_firstaudit_date());
			pstmto.setString(16,tGSOrderBean.getGs_firstaudit_advices());
			pstmto.setString(17,tGSOrderBean.getGs_reviewer_id());
			pstmto.setString(18,tGSOrderBean.getGs_review_date());
			pstmto.setString(19,tGSOrderBean.getGs_review_advices());								
			
			pstmto.executeUpdate();

			//4 提交订单明细
			pstmt.executeBatch();
		
			//5 关闭
			if(pstmt!=null) pstmt.close();
			if(pstmto!=null) pstmto.close();			
			
		}catch(Exception ex){
			GSLogWriter.error(this,ex.getMessage());
			throw new GSException(ex.getMessage());
		}
		return returnValue;
	}	
	// add by Jackey end
}
